﻿/**********************************弘石项目*******************************************
* 功能描述:合同管理
* 作者:刘子奇
* 时间:2021-12-22
* 
**********************************弘石项目*******************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using HongStone.Domain.ClientManagementModel;
using HongStone.Domain.ContractModel;
using HongStone.Domain.OperationsManagement;
using HongStone.Domain.SystemSetupModel.FrequentlyUsedDataModel;
using HongStone.Ince;

namespace HongStone.Repository
{
   public class contractDal:IContractInce
    {
        private readonly HongStoneContext db;
        public contractDal(HongStoneContext hongStoneContext)
        {
            db = hongStoneContext;
        }
       /// <summary>
       /// 合同数据显示
       /// </summary>
       /// <returns></returns>
        public List<Thecontract> GetThecontracts(out int totalcount,int pageindex,int pagesize,string ContractName,string Constructionunit,string Originalamount,int  ContractclassificationId)
        {
            var query=db.thecontracts.ToList();
            if(!string.IsNullOrEmpty(ContractName))
            {
                query = query.Where(p => p.ContractName.Contains(ContractName)).ToList();
            }
            if (!string.IsNullOrEmpty(Constructionunit))
            {
                query=query.Where(p=>p.Constructionunit.Contains(Constructionunit)).ToList();
            }
            if (!string.IsNullOrEmpty(Originalamount))
            {
                query = query.Where(p => p.Originalamount.Contains(Originalamount)).ToList();
            }

            totalcount = query.Count();
            var list = query.Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();
            return list;
        }
        /// <summary>
        /// 字典数据
        /// </summary>
        /// <returns></returns>
        public List<DictionariesTable> GetZiDian()
        {
            return db.dictionariesTables.ToList();
        }
        /// <summary>
        /// 建筑分类
        /// </summary>
        /// <returns></returns>
        public List<ArchClass> GetarcClass()
        {
            return GetarcClasses(db.architectureClass.ToList());
        }

        public List<ArchClass> GetarcClasses(List<ArchitectureClass> classes,int AId=0)
        {
            return classes.Where(p => p.AId == AId).Select(p => new ArchClass
            {
                ArchitectureId=p.ArchitectureId,
                ArchitectureName=p.ArchitectureName,
                Childarchites=GetarcClasses(classes,p.ArchitectureId)
            }).ToList();
        }
        /// <summary>
        /// 结构形式
        /// </summary>
        /// <returns></returns>
        public List<Strclass> GetStrstyle()
        {
            return GetStrstyles(db.structuralstyles.ToList());
        }

        public List<Strclass> GetStrstyles(List<Structuralstyle> structuralstyles, int PId = 0)
        {
            return structuralstyles.Where(p => p.PId == PId).Select(p => new Strclass
            {
                 StructuralstyleId= p.StructuralstyleId,
                ProjectLeaderName = p.ProjectLeaderName,
                Childstyle = GetStrstyles(structuralstyles, p.StructuralstyleId)
            }).ToList();
        }
        /// <summary>
        /// 合同信息添加
        /// </summary>
        /// <param name="thecontract"></param>
        /// <returns></returns>
        public int AddContract(Thecontract thecontract)
        {
            db.thecontracts.Add(thecontract);
            return db.SaveChanges();
        }
        /// <summary>
        /// 工程负责人信息
        /// </summary>
        /// <returns></returns>
        public List<ProjectLeader> GetProjectLeaders()
        {
            return db.projectLeaders.ToList();
        }
        /// <summary>
        /// 收费列表详细
        /// </summary>
        /// <returns></returns>
        public List<charge> GetCharges()
        {
            return db.charges.ToList();
        }
        /// <summary>
        /// 收费数据反填
        /// </summary>
        /// <param name="CId"></param>
        /// <returns></returns>
        public charge Editcharge(int sid)
        {
            return db.charges.Find(sid);
        }
        /// <summary>
        /// 收费数据修改
        /// </summary>
        /// <param name="charge"></param>
        /// <returns></returns>
        public int Updatecharge(charge charge)
        {
            db.Entry(charge).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            return db.SaveChanges();
        }
        /// <summary>
        /// 收费列表信息添加
        /// </summary>
        /// <param name="charge"></param>
        /// <returns></returns>
        public int AddCharges(charge charge)
        {
            db.charges.Add(charge);
            return db.SaveChanges();
        }
        /// <summary>
        /// 收费列表删除
        /// </summary>
        /// <param name="Cid"></param>
        /// <returns></returns>
        public int DelCharges(int Cid) 
        {
            var i = db.charges.Find(Cid);
            db.Remove(i);
            return db.SaveChanges();
        }
        /// <summary>
        /// 反填客户数据
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public Customer GetCustomer(int CustomerId)
        {
          return db.customers.Find(CustomerId);  
        }
        /// <summary>
        /// 客户信息显示
        /// </summary>
        /// <returns></returns>
        public List<Customer> GetCustomers()
        {
            return db.customers.ToList();
        }
        /// <summary>
        /// 合同数据批量删除
        /// </summary>
        /// <param name="Ids"></param>
        /// <returns></returns>
        public int DelAllThecont(string Ids)
        {
            var arr = db.thecontracts.Where(p => Ids.Contains(Convert.ToString(p.ContractId)));
            db.thecontracts.RemoveRange(arr);
            return db.SaveChanges();
        }
        /// <summary>
        /// 合同数据反填
        /// </summary>
        /// <param name="ContractId"></param>
        /// <returns></returns>
        public Thecontract EditThecontract(int ContractId)
        {
            return db.thecontracts.Find(ContractId);
        }
        /// <summary>
        /// 项目信息
        /// </summary>
        /// <returns></returns>
        public List<Project> GetProjects()
        {
            return db.projects.ToList();
        }
        /// <summary>
        /// 查询合同金额信息
        /// </summary>
        /// <returns></returns>
        public List<HTjine> Getjine()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<HTjine>("select count(ContractId) as Thecount,count(Booked) as fkcount,count(b.CId) as jscount,sum(Booked) as ze,sum(Actualamount) as sk from Thecontract as a join charges as b on a.CId=b.CId ").ToList();
                return list;
            }
        }

        public List<jidu> Getjidu()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<jidu>($"select * from (select a.ContractId as TollId, CAST((select count(CId) from charges) as varchar(100)) as 签订合同数, CAST((select sum(Actualamount) from Thecontract) as varchar(100)) as 签订合同额,CAST((select sum(Booked) from charges) AS VARCHAR(100))as 收款额,case when Contractsigningdate>= '2021-1-1' and Contractsigningdate<= '2021-3-31' then '第一季度' when Contractsigningdate>= '2021-4-1' and Contractsigningdate<= '2021-6-30' then '第二季度' when Contractsigningdate>= '2021-7-1' and Contractsigningdate<= '2021-9-30' then '第三季度' when Contractsigningdate>= '2021-10-01' and Contractsigningdate<= '2021-12-31' then '第四季度' else '查询不到' end as 季度1 from Thecontract as a join charges as b on a.CId = b.CId group by a.ContractId,Contractsigningdate,case when Contractsigningdate>= '2021-1-1' and Contractsigningdate<= '2021-3-31' then '第一季度' when Contractsigningdate>= '2021-4-1' and Contractsigningdate<= '2021-6-30' then '第二季度' when Contractsigningdate>= '2021-7-1' and Contractsigningdate<= '2021-9-30' then '第三季度' when Contractsigningdate>= '2021-10-01' and Contractsigningdate<= '2021-12-31' then '第四季度' else '查询不到' end) T unpivot( diyi for jidus  in (签订合同数, 签订合同额, 收款额) ) pvts " +
                    "pivot( count(TollId) for 季度1  in (第一季度, dier, disan, disi) ) pvt");
                return list.ToList();
            }
        }
        /// <summary>
        /// 按月份查询
        /// </summary>
        /// <returns></returns>
        public List<anyue> GetAnyues()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<anyue>("p_Mothys2", CommandType.StoredProcedure).ToList();
                return list;
            }
        }

    }
}
